package com.cn.Dao;

import com.cn.base.BaseDao;
import com.cn.base.Util;
import com.cn.entity.BookInfo;
import com.cn.entity.BookUnion;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public static List<BookInfo> findAll() throws SQLException {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from book";
            ResultSet rs = state.executeQuery(sql);
            List<BookInfo> bookInfos = getList(rs);
            connection.close();
            return bookInfos;
        }catch (Exception e){
            throw e;
        }
    }
    public List<BookUnion> findAllUnionLike(String bk,String yh,int bm,int zt)throws SQLException{
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select book.BOOK_ID,user.USER_USERNAME,newspaper_info.NEWSINFO_NAME,book.BOOK_TOTAL_PRICE," +
                    "book.BOOK_STATE,book.BOOK_AMOUNT,book.BOOK_MONTH from book,newspaper_info,user,newspaper,department where book.NEWSINFO_NUM=" +
                    "newspaper_info.NEWSINFO_NUM and book.USER_ID=user.USER_ID and user.DEP_ID=department.DEP_ID" +
                    " and newspaper_info.NEWS_ID=newspaper.NEWS_ID and (newspaper_info.NEWSINFO_NAME like  '"+bk+"' or " +
                    "newspaper_info.NEWSINFO_NUM like '"+bk+"') and (user.USER_USERNAME like '"+yh+"' or user.USER_NAME like '"+yh+"') ";
            if(bm!=-1)sql+=" and department.DEP_ID='"+bm+"'";
            if(zt!=-1)sql+=" and book.BOOK_STATE='"+zt+"'";
            System.out.println(sql);
            ResultSet rs = state.executeQuery(sql);
            List<BookUnion> bookUnionList = getBookUnionList(rs);
            connection.close();
            return bookUnionList;
        }catch (Exception e){
            throw e;
        }
    }
    public static List<BookInfo> findByUser(int userID)throws SQLException{
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from book where USER_ID ='" + userID+"'";
            System.out.println(sql);
            ResultSet rs = state.executeQuery(sql);
            List<BookInfo> bookInfos = getList(rs);
            connection.close();
            return bookInfos;
        }catch (Exception e){
            throw e;
        }
    }

    public static List<BookUnion> findAllUnion()throws SQLException{
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select book.BOOK_ID,user.USER_USERNAME,newspaper_info.NEWSINFO_NAME,book.BOOK_TOTAL_PRICE," +
                    "book.BOOK_STATE,book.BOOK_AMOUNT,book.BOOK_MONTH from book,newspaper_info,user,newspaper,department where book.NEWSINFO_NUM=" +
                    "newspaper_info.NEWSINFO_NUM and book.USER_ID=user.USER_ID and user.DEP_ID=department.DEP_ID" +
                    " and newspaper_info.NEWS_ID=newspaper.NEWS_ID ";
            ResultSet rs = state.executeQuery(sql);
            List<BookUnion> bookUnionList = getBookUnionList(rs);
            connection.close();
            return bookUnionList;
        }catch (Exception e){
            throw e;
        }
    }
    public BookInfo findById(int id) throws SQLException{
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from book where BOOK_ID='"+id+"'";
            ResultSet rs = state.executeQuery(sql);
            BookInfo bookInfo = getBook(rs);
            connection.close();
            return bookInfo;
        }catch (Exception e){
            throw e;
        }
    }
    public List<BookUnion> findAllByUser(String yh)throws SQLException{
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select book.BOOK_ID,user.USER_USERNAME,newspaper_info.NEWSINFO_NAME,book.BOOK_TOTAL_PRICE," +
                    "book.BOOK_STATE,book.BOOK_AMOUNT,book.BOOK_MONTH from book,newspaper_info,user,newspaper,department where book.NEWSINFO_NUM=" +
                    "newspaper_info.NEWSINFO_NUM and book.USER_ID=user.USER_ID and user.DEP_ID=department.DEP_ID" +
                    " and newspaper_info.NEWS_ID=newspaper.NEWS_ID and user.USER_USERNAME = '"+yh+"'  ";

            System.out.println(sql);
            ResultSet rs = state.executeQuery(sql);
            List<BookUnion> bookUnionList = getBookUnionList(rs);
            connection.close();
            return bookUnionList;
        }catch (Exception e){
            throw e;
        }
    }
    private static List<BookUnion> getBookUnionList(ResultSet resultSet){
        try {
            List<BookUnion> bookUnions=new ArrayList<>();
            while (resultSet.next()){
                BookUnion bookUnion = new BookUnion();
                bookUnion.setBid(resultSet.getInt(1));
                bookUnion.setUsername(resultSet.getString(2));
                bookUnion.setNewsname(resultSet.getString(3));
                bookUnion.setPrice(resultSet.getFloat(4));
                bookUnion.setState(resultSet.getInt(5));
                bookUnion.setAmount(resultSet.getInt(6));
                bookUnion.setMonth(resultSet.getInt(7));
                bookUnions.add(bookUnion);
            }
            return bookUnions;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private BookInfo getBook(ResultSet resultSet){
        try {
            BookInfo bookInfo = new BookInfo();
            while (resultSet.next()){
                bookInfo.setBid(resultSet.getInt(1));
                bookInfo.setNum(resultSet.getString(2));
                bookInfo.setAmount(resultSet.getInt(3));
                bookInfo.setMouth(resultSet.getInt(4));
                bookInfo.setTotal_price(resultSet.getFloat(5));
                bookInfo.setTime(resultSet.getDate(6));
                bookInfo.setState(resultSet.getInt(7));
                bookInfo.setUserid(resultSet.getInt(8));
            }
            return bookInfo;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    private static List<BookInfo> getList(ResultSet resultSet){
        try {
            List<BookInfo> bookInfos=new ArrayList<>();
            while (resultSet.next()){
                BookInfo bookInfo = new BookInfo();
                bookInfo.setBid(resultSet.getInt(1));
                bookInfo.setNum(resultSet.getString(2));
                bookInfo.setAmount(resultSet.getInt(3));
                bookInfo.setMouth(resultSet.getInt(4));
                bookInfo.setTotal_price(resultSet.getFloat(5));
                bookInfo.setTime(resultSet.getDate(6));
                bookInfo.setState(resultSet.getInt(7));
                bookInfo.setUserid(resultSet.getInt(8));
                bookInfos.add(bookInfo);
            }
            return bookInfos;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public void Insert(BookInfo bookInfo) throws SQLException {
        Connection connection = BaseDao.getConn();
        try{
            Statement state = connection.createStatement();//容器
            String sql = "insert into book (NEWSINFO_NUM, BOOK_AMOUNT, BOOK_MONTH, BOOK_TOTAL_PRICE, BOOK_TIME,BOOK_STATE,USER_ID) value ('" +
                    bookInfo.getNum() + "','"+
                    bookInfo.getAmount() + "','" +
                    bookInfo.getMouth() + "','" +
                    bookInfo.getTotal_price() +"','" +
                    Util.changeDate(bookInfo.getTime()) +"','" +
                    bookInfo.getState() +"','" +
                    bookInfo.getUserid() +
                    "')";
            System.out.println("Insert book sql\n"+sql);
            state.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            connection.close();
        }
    }
    public void UpdateById(Integer id, Integer s) {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="UPDATE book set BOOK_STATE='"+s+"' where BOOK_ID='"+id+"'";
            state.executeUpdate(sql);
            connection.close();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
